ALTER TABLE service ADD COLUMN short_name character varying(25);
ALTER TABLE service ADD COLUMN full_name character varying(255);
ALTER TABLE service ADD COLUMN foreign_name character varying(255);
ALTER TABLE service ADD COLUMN code character varying(20);
ALTER TABLE service ADD COLUMN description character varying;
ALTER TABLE service ADD COLUMN id_default_unit integer;

DROP FUNCTION sp_service_count(integer, character varying, integer, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_service_insert(character varying, integer, integer, integer);
DROP FUNCTION sp_service_selectlist(integer, integer, integer, character varying, integer, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_service_update(integer, character varying, integer, integer, integer, boolean, timestamp without time zone);

CREATE OR REPLACE FUNCTION sp_service_insert(
	_name	character varying(255) DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null,
	_short_name	character varying(25) DEFAULT null,
	_full_name	character varying(255) DEFAULT null,
	_foreign_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_default_unit	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO service(name,status,notification,creator,updator,deletion_time,short_name,full_name,foreign_name,code,description,id_default_unit)
	VALUES(_name,_status,_notification,_creator,_creator,null,_short_name,_full_name,_foreign_name,_code,_description,_id_default_unit);
RETURN currval('service_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_service_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_short_name	character varying(25) DEFAULT null,
	_full_name	character varying(255) DEFAULT null,
	_foreign_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_default_unit	integer DEFAULT null
)
RETURNS SETOF service
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM service WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_short_name is null OR short_name = _short_name)	
	AND (_full_name is null OR full_name = _full_name)	
	AND (_foreign_name is null OR foreign_name = _foreign_name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_id_default_unit is null OR id_default_unit = _id_default_unit)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_service_count (
	_id	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_short_name	character varying(25) DEFAULT null,
	_full_name	character varying(255) DEFAULT null,
	_foreign_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_default_unit	integer DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM service WHERE
	 	(_id is null OR id = _id)	
	AND (_name is null OR name = _name)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_short_name is null OR short_name = _short_name)	
	AND (_full_name is null OR full_name = _full_name)	
	AND (_foreign_name is null OR foreign_name = _foreign_name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_id_default_unit is null OR id_default_unit = _id_default_unit);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_service_update 
(	_id integer,
	_name character varying(255),
	_status integer,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_short_name character varying(25),
	_full_name character varying(255),
	_foreign_name character varying(255),
	_code character varying(20),
	_description character varying,
	_id_default_unit integer  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE service 
	SET 		name = _name,
		status = _status,
		notification = _notification,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		short_name = _short_name,
		full_name = _full_name,
		foreign_name = _foreign_name,
		code = _code,
		description = _description,
		id_default_unit = _id_default_unit
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
